hive -e "
use credit;

set hive.exec.mode.local.auto=true;

insert overwrite table dm_rk
select
    t1.addrname,
    t1.product_name,
    t1.s_amt,
    rank() over(order by t1.s_amt desc) r_n,
    current_timestamp
from
(
    select
        nvl(a.addrname,'其他地区') addrname,
        nvl(p.product_name, '其他产品') product_name,
        sum(c.txt_amt) s_amt
    from dw_cust_info c left join dw_addr_info a
        on c.addrno = a.addrno
        left join dw_product_info p
        on c.product_code = p.product_code
    group by a.addrname, p.product_name
) t1;
"
